﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace Ex10.ExtendedTransactions
{
    public class ExtendedTransactions
    {
        private const string CONNECTION_STRING = "Server=.\\SQLEXPRESS;" +
    " Database=ATM; Integrated Security=true";

        public static void Main()
        {
            string cardNumber = "12aa21a";
            int pinCode = 1234;
            decimal moneyToDraw = 200M;

            DrawMoney(cardNumber, pinCode, moneyToDraw);
        }

        private static void DrawMoney(string cardNumber, int pinCode, decimal moneyToDraw)
        {
            SqlConnection dbCon = new SqlConnection(CONNECTION_STRING);
            dbCon.Open();
            using (dbCon)
            {
                SqlTransaction trans = dbCon.BeginTransaction(System.Data.IsolationLevel.RepeatableRead);
                Console.WriteLine("Transaction started...");

                SqlCommand cmd = dbCon.CreateCommand();
                cmd.Transaction = trans;

                try
                {
                    cmd.CommandText = "SELECT * FROM CardAccounts " +
                        "WHERE CardNumber = @cardNumber";
                    cmd.Parameters.Add(new SqlParameter("@cardNumber", cardNumber));

                    SqlDataReader reader = cmd.ExecuteReader();

                    int cardPIN, cardID;
                    decimal moneyInCard;

                    bool rollback = 
                        GetInfoForCard(trans, reader, out cardPIN, out cardID, out moneyInCard);

                    if (rollback)
                    {
                        Console.WriteLine("Invalid card number!");
                        RollBackTran(trans);
                    }
                    else
                    {
                        if (cardPIN != pinCode)
                        {
                            Console.WriteLine("Invalid pin code!");
                            RollBackTran(trans);
                        }
                        else
                        {
                            if (moneyInCard < moneyToDraw)
                            {
                                Console.WriteLine("Not enought money to draw!");
                                RollBackTran(trans);
                            }
                            else
                            {
                                decimal newMoneyInCard = (decimal)moneyInCard - moneyToDraw;
                                Console.WriteLine("You draw {0}$.", moneyToDraw);

                                UpdateMoneyLeftInCard(cmd, cardID, newMoneyInCard);

                                WriteWithdraw(moneyToDraw, cmd);

                                trans.Commit();
                                Console.WriteLine("Transaction comitted.");
                            }
                        }
                    }
                }
                catch (SqlException e)
                {
                    Console.WriteLine("Exception occured: {0}", e.Message);
                    RollBackTran(trans);
                }
            }
        }

        private static void WriteWithdraw(decimal moneyToDraw, SqlCommand cmd)
        {
            cmd.CommandText = "INSERT INTO AccountTransactions([CardNumber], [TransactionDate],[Amount]) " +
                "VALUES(@cardNumber, @tranDate, @moneyToDraw) ";
            cmd.Parameters.Add(new SqlParameter("@moneyToDraw", moneyToDraw));
            cmd.Parameters.Add(new SqlParameter("@tranDate", DateTime.Now));
            cmd.ExecuteNonQuery();
        }

        private static void UpdateMoneyLeftInCard(SqlCommand cmd, int cardID, decimal newMoneyInCard)
        {
            cmd.CommandText = "UPDATE CardAccounts " +
                "SET CardCash = @newValue " +
                "WHERE CardID = @cardID";
            cmd.Parameters.Add(new SqlParameter("@newValue", newMoneyInCard));
            cmd.Parameters.Add(new SqlParameter("@cardID", cardID));
            cmd.ExecuteNonQuery();
        }

        private static bool GetInfoForCard(SqlTransaction trans, SqlDataReader reader,
            out int cardPIN, out int cardID, out decimal moneyInCard)
        {
            bool rollback = false;
            using (reader)
            {
                if (reader.Read())
                {
                    cardPIN = int.Parse(reader["CardPIN"].ToString());
                    cardID = (int)reader["CardID"];
                    moneyInCard = (decimal)reader["CardCash"];
                }
                else
                {
                    cardID = 0;
                    cardPIN = 0;
                    moneyInCard = 0;
                    rollback = true;
                }
            }
            return rollback;
        }

        private static void RollBackTran(SqlTransaction trans)
        {
            trans.Rollback();
            Console.WriteLine("Transaction cancelled.");
        }
    }
}
